<?php
if (!defined('ABSPATH')) {
    exit();
}
class CMC_Coins_historical extends PW_DB
{

    /**
     * Get things started
     *
     * @access  public
     * @since   1.0
     */
    public function __construct()
    {

        global $wpdb;

        $this->table_name = $wpdb->base_prefix . CMC_HISTORICAL_DB;
        $this->primary_key = 'id';
        $this->version = '1.0';

    }

    /**
     * Get columns and formats
     *
     * @access  public
     * @since   1.0
     */
    public function get_columns()
    {

        return array(
            'id' => '%d',
            'coin_id' => '%s',
            'one_day_historical' => '%s',
            'one_year_historical' => '%s',
        );
    }
    /**
     * Insert historical meta data into the database
     *
     * @param string $coins_data The historical data to be inserted
     * @param string $coin_id The ID of the coin
     * @param int $day The day value
     */
    public function cmc_historical_meta_insert($coins_data, $coin_id, $day)
    {
        global $wpdb;

        // Check if the coin exists by ID
        $coinid = $this->coin_exists_by_id($coin_id);

        // Determine the historical period based on the day value
        $no_day = ($day == 2) ? "one_day_historical" : "one_year_historical";

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coins_data = sanitize_text_field($coins_data);
        $coin_id = sanitize_text_field($coin_id);

        // Perform the database update based on the coin's existence
        if ($coinid == false) {
            $this->cmc_single_coin_id($coin_id);
            $data = $wpdb->query($wpdb->prepare("UPDATE " . $this->table_name . " SET " . $no_day . " = %s WHERE coin_id = %s", $coins_data, $coin_id));
        } else {
            $data = $wpdb->query($wpdb->prepare("UPDATE " . $this->table_name . " SET " . $no_day . " = %s WHERE coin_id = %s", $coins_data, $coin_id));
        }
    }
    /**
     * Get historical data for a specific coin
     *
     * @param string $coin_id The ID of the coin
     * @param int $day The day value
     * @return mixed|null Historical data for the coin
     */
    public function cmc_get_historical_data($coin_id, $day)
    {
        global $wpdb;

        // Determine the historical period based on the day value
        $no_day = ($day == 2) ? "one_day_historical" : "one_year_historical";

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coin_id = sanitize_text_field($coin_id);

        // Perform the database query to retrieve historical data
        $results = $wpdb->get_results(
            $wpdb->prepare("SELECT " . $no_day . " FROM $this->table_name WHERE coin_id = %s", $coin_id)
        );

        // Return the historical data after decoding from JSON
        if (!empty($results) && isset($results[0]->$no_day)) {
            return json_decode($results[0]->$no_day);
        } else {
            return null;
        }

    }

    /**
     * Get default column values
     *
     * @access  public
     * @since   1.0
     */
    public function get_column_defaults()
    {
        return array(
            'coin_id' => '',
            'one_day_historical' => '',
            'one_year_historical' => '',
            'last_updated' => date('Y-m-d H:i:s'),
        );
    }

    /**
     * Check if a coin exists by its ID
     *
     * @param string $coin_ID The ID of the coin
     * @return bool Whether the coin exists or not
     */
    public function coin_exists_by_id($coin_ID)
    {
        global $wpdb;

        // Sanitize and escape user input for a database query and use $wpdb->prepare() for insertion
        $coin_ID = esc_sql($coin_ID);

        // Perform the database query to check the existence of the coin
        $count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $this->table_name WHERE coin_id = %s", $coin_ID));

        // Return the result of the existence check
        return ($count == 1) ? true : false;
    }

    /**
     * Insert or update a single coin ID into the database
     *
     * @param string $coins_data The coin ID to be inserted or updated
     * @return int|false The number of rows affected or false on error
     */
    public function cmc_single_coin_id($coins_data)
    {
        global $wpdb;

        // Build the query with proper placeholder
        $query = $wpdb->prepare(
            "INSERT INTO `" . $this->table_name . "` (`coin_id`) VALUES (%s) ON DUPLICATE KEY UPDATE coin_id=VALUES(coin_id)",
            $coins_data
        );

        // Execute the prepared query
        $result = $wpdb->query($query);
        return $result;
    }
    /**
     * Insert coin IDs into the database after sanitizing and escaping the input data
     *
     * @param array $coins_data The array of coin data to be inserted
     * @return int|false The number of rows affected or false on error
     */
    public function cmc_insert_coin_ids($coins_data)
    {
        if (is_array($coins_data) && count($coins_data) > 1) {
            global $wpdb;

            // Build the base query
            $query_indexes = "INSERT INTO `" . $this->table_name . "` (`coin_id`) VALUES ";
            $placeholders = array();
            $values = array();

            foreach ($coins_data as $coin) {
                // Add placeholder for each row
                $placeholders[] = '(%s)';
                $values[] = $coin['coin_id'];
            }

            // Combine the query with placeholders
            $query = $query_indexes . implode(',', $placeholders) . " ON DUPLICATE KEY UPDATE coin_id=VALUES(coin_id)";

            // Prepare and execute the query safely
            $prepared_query = $wpdb->prepare($query, $values);
            return $wpdb->query($prepared_query);
        }
        return false;
    }

    /**
     * Return the number of results found for a given query
     *
     * @param  array  $args
     * @return int
     */
    public function count($args = array())
    {
        return $this->get_coins($args, true);
    }

    /**
     * Create the table
     *
     * @access  public
     * @since   1.0
     */
    public function create_table()
    {
        global $wpdb;

        // Include the necessary file for database table creation
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Define the SQL query for creating the table with proper sanitization and escaping
        $sql = "CREATE TABLE IF NOT EXISTS " . esc_sql($this->table_name) . " (
		id bigint(20) NOT NULL AUTO_INCREMENT,
		coin_id varchar(200) NOT NULL,
		one_day_historical longtext NOT NULL,
		one_year_historical longtext NOT NULL,
		last_updated TIMESTAMP NOT NULL DEFAULT NOW() ON UPDATE NOW(),
		PRIMARY KEY (id),
		UNIQUE (coin_id)
		) CHARACTER SET utf8 COLLATE utf8_general_ci;";

        // Execute the sanitized database query
        $wpdb->query($sql);

        // Update the database version option with proper sanitization
        update_option(esc_sql($this->table_name) . '_db_version', esc_sql($this->version));
    }

    /**
     * Drop the table from the database
     *
     * @access  public
     * @since   1.0
     */
    public function drop_table()
    {
        global $wpdb;

        // Drop the table if it exists after proper sanitization and escaping
        $wpdb->query('DROP TABLE IF EXISTS ' . esc_sql($this->table_name));

    }
}
